import sys
import os
import logging
import re
import json
from typing import Dict, List, Any, Optional, Tuple

# 添加项目根路径到 sys.path
sys.path.append(os.path.join(os.path.dirname(os.path.abspath(__file__)), '..', '..'))

# 导入 MySQLUtil
from shared.utils.MySQLUtil import MySQLUtil

# 设置日志
logger = logging.getLogger(__name__)

def get_各学历及总体_对母校的满意度(
    project_id: int,
    questionnaire_ids: List[int],
    product_code: Optional[str] = None,
    project_code: Optional[str] = None,
    region_code: Optional[str] = None
) -> Dict[str, Any]:
    """
    各学历及总体，对母校的满意度 - 指标计算函数
    
    ## 指标说明
    该指标计算各学历层次及总体毕业生对母校的满意度情况，通过统计问卷中"我对母校的总体满意度"问题前三项选择人数占比来反映。
    计算方式为：(很满意人数+满意人数+基本满意人数)/(总有效回答人数)
    
    ## Args
        project_id (int): 项目ID，用于查询项目配置信息
        questionnaire_ids (List[int]): 问卷ID集合，用于确定数据范围
        product_code (Optional[str]): 产品编码，用于路由到特定计算逻辑
        project_code (Optional[str]): 项目编码，用于路由到特定计算逻辑
        region_code (Optional[str]): 区域编码，用于路由到特定计算逻辑
        
    ## 示例
    ### 输入
    ```json
    {
        "project_id": 5895,
        "questionnaire_ids": [11158, 11159]
    }
    ```
    
    ### 输出
    ```json
    {
        "success": true,
        "message": "ok",
        "code": 0,
        "result": {
            "summary": {
                "overall_satisfaction_ratio": 0.9678,
                "total_count": 2500,
                "satisfied_count": 2420,
                "description": "总体对母校满意度为 96.78%"
            },
            "by_education": [
                {
                    "education": "本科毕业生",
                    "type": "grouped",
                    "satisfaction_ratio": 0.9646,
                    "total_count": 1800,
                    "satisfied_count": 1736,
                    "percentage": "96.46%",
                    "options_detail": [
                        {"option": "很满意", "count": 900, "ratio": 0.5000, "percentage": "50.00%"},
                        {"option": "满意", "count": 540, "ratio": 0.3000, "percentage": "30.00%"},
                        {"option": "基本满意", "count": 296, "ratio": 0.1646, "percentage": "16.46%"},
                        {"option": "不满意", "count": 45, "ratio": 0.0250, "percentage": "2.50%"},
                        {"option": "很不满意", "count": 19, "ratio": 0.0104, "percentage": "1.04%"}
                    ]
                }
            ],
            "overall": {
                "education": "总体",
                "type": "summary",
                "satisfaction_ratio": 0.9678,
                "total_count": 2500,
                "satisfied_count": 2420,
                "percentage": "96.78%",
                "options_detail": [
                    {"option": "很满意", "count": 1250, "ratio": 0.5000, "percentage": "50.00%"},
                    {"option": "满意", "count": 750, "ratio": 0.3000, "percentage": "30.00%"},
                    {"option": "基本满意", "count": 420, "ratio": 0.1678, "percentage": "16.78%"},
                    {"option": "不满意", "count": 55, "ratio": 0.0220, "percentage": "2.20%"},
                    {"option": "很不满意", "count": 25, "ratio": 0.0102, "percentage": "1.02%"}
                ]
            },
            "legacy_format": [
                {
                    "calcVals": [
                        {
                            "val": [
                                {"key": "本科毕业生", "val": "0.9646"},
                                {"key": "专科毕业生", "val": "0.9532"},
                                {"key": "硕士研究生", "val": "0.9721"},
                                {"key": "博士研究生", "val": "0.9812"},
                                {"key": "总体", "val": "0.9678"}
                            ]
                        }
                    ]
                }
            ]
        }
    }
    ```
    """
    logger.info(f"开始计算指标: 各学历及总体，对母校的满意度, 项目ID: {project_id}")
    
    try:
        db = MySQLUtil()  

        # 1. 查询项目配置信息
        project_sql = """
        SELECT client_code, item_year, dy_target_items, split_tb_paper 
        FROM client_item 
        WHERE id = %s
        """
        project_info = db.fetchone(project_sql, (project_id,))
        if not project_info:
            raise ValueError(f"未找到项目ID={project_id}的配置信息")

        client_code = project_info['client_code']
        item_year = project_info['item_year']
        split_tb_paper = project_info['split_tb_paper']
        
        logger.info(f"项目配置: client_code={client_code}, item_year={item_year}, split_tb_paper={split_tb_paper}")

        # 2. 计算 shard_tb_key
        shard_tb_key = re.sub(r'^[A-Za-z]*0*', '', client_code)
        logger.info(f"计算得到 shard_tb_key: {shard_tb_key}")

        # 3. 查询问卷信息
        questionnaire_sql = f"""
        SELECT id, dy_target 
        FROM wt_template_customer 
        WHERE id IN ({','.join(['%s'] * len(questionnaire_ids))})
        """
        questionnaires = db.fetchall(questionnaire_sql, tuple(questionnaire_ids))
        if not questionnaires:
            raise ValueError(f"未找到问卷ID集合={questionnaire_ids}的配置信息")
        
        logger.info(f"查询到问卷信息: {questionnaires}")

        # 4. 过滤特定调研对象的问卷
        valid_questionnaire_ids = [q['id'] for q in questionnaires if q['dy_target'] == 'GRADUATE_SHORT']
        if not valid_questionnaire_ids:
            raise ValueError("未找到目标调研对象的问卷ID")
            
        logger.info(f"找到有效问卷ID: {valid_questionnaire_ids}")

        # 5. 查询问题信息
        question_sql = """
        SELECT id, wt_code, wt_obj 
        FROM wt_template_question_customer 
        WHERE cd_template_id = %s AND wt_code = %s AND is_del = 0
        """
        question_info = db.fetchone(question_sql, (valid_questionnaire_ids[0], 'T00000444'))
        if not question_info:
            raise ValueError("未找到指定问题编码的问题信息")
            
        logger.info(f"找到问题信息: {question_info['id']}")

        # 6. 解析问题选项
        wt_obj = json.loads(question_info['wt_obj'])
        options = []
        for item in wt_obj['itemList']:
            options.append({
                'key': item['key'],
                'val': item['val'],
                'weight': item.get('weight', 1)
            })

        # 7. 构建动态表名
        answer_table = f"re_dy_paper_answer_{split_tb_paper}"
        student_table = f"dim_client_target_baseinfo_student_{item_year}"

        # 8. 定义计算函数
        def _calculate_detailed_data(education: Optional[str] = None) -> Dict[str, Any]:
            """计算指定学历层次的详细满意度数据"""
            education_condition = "AND s.education = %s" if education else ""
            params = [valid_questionnaire_ids[0], question_info['id'], shard_tb_key, item_year]
            if education:
                params.append(education)
            
            sql = f"""
            SELECT
                SUM(t1.c1) as c1,
                SUM(t1.c2) as c2,
                SUM(t1.c3) as c3,
                SUM(t1.c4) as c4,
                SUM(t1.c5) as c5
            FROM {answer_table} t1
            JOIN {student_table} s ON t1.target_no = s.target_no
            WHERE
                t1.cd_template_id = %s
                AND t1.wid = %s
                AND t1.ans_true = 1
                AND s.shard_tb_key = %s
                AND s.item_year = %s
                {education_condition}
            """
            
            result = db.fetchone(sql, tuple(params))
            if not result:
                return {
                    "satisfaction_ratio": 0.0,
                    "total_count": 0,
                    "satisfied_count": 0,
                    "options_detail": []
                }
                
            # 计算各选项数据
            counts = [int(result[f'c{i}'] or 0) for i in range(1, 6)]
            total_count = sum(counts)
            satisfied_count = sum(counts[:3])  # 前三项
            satisfaction_ratio = satisfied_count / total_count if total_count > 0 else 0.0
            
            # 构建选项明细
            option_names = ["很满意", "满意", "基本满意", "不满意", "很不满意"]
            options_detail = []
            for i, (count, name) in enumerate(zip(counts, option_names)):
                ratio = count / total_count if total_count > 0 else 0.0
                options_detail.append({
                    "option": name,
                    "count": count,
                    "ratio": round(ratio, 4),
                    "percentage": f"{ratio * 100:.2f}%"
                })
            
            return {
                "satisfaction_ratio": round(satisfaction_ratio, 4),
                "total_count": total_count,
                "satisfied_count": satisfied_count,
                "options_detail": options_detail
            }

        # 9. 计算各学历层次及总体满意度
        education_levels = ['本科毕业生', '专科毕业生', '硕士研究生', '博士研究生']
        
        # 计算总体数据
        overall_data = _calculate_detailed_data()
        
        # 计算各学历层次数据
        by_education = []
        legacy_results = []
        
        for level in education_levels:
            data = _calculate_detailed_data(level)
            
            # 构建分组数据
            by_education.append({
                "education": level,
                "type": "grouped",
                "satisfaction_ratio": data["satisfaction_ratio"],
                "total_count": data["total_count"],
                "satisfied_count": data["satisfied_count"],
                "percentage": f"{data['satisfaction_ratio'] * 100:.2f}%",
                "options_detail": data["options_detail"]
            })
            
            # 构建兼容格式
            legacy_results.append({
                "key": level,
                "val": f"{data['satisfaction_ratio']:.4f}"
            })
        
        # 添加总体到兼容格式
        legacy_results.append({
            "key": "总体",
            "val": f"{overall_data['satisfaction_ratio']:.4f}"
        })
        
        logger.info(f"总体满意度: {overall_data['satisfaction_ratio']:.4f}, 总计 {overall_data['total_count']} 人")
        logger.info(f"各学历满意度: {[(item['education'], item['satisfaction_ratio'], item['total_count']) for item in by_education]}")

        logger.info(f"指标 '各学历及总体，对母校的满意度' 计算成功")
        return {
            "success": True,
            "message": "ok",
            "code": 0,
            "result": {
                "summary": {
                    "overall_satisfaction_ratio": overall_data["satisfaction_ratio"],
                    "total_count": overall_data["total_count"],
                    "satisfied_count": overall_data["satisfied_count"],
                    "description": f"总体对母校满意度为 {overall_data['satisfaction_ratio'] * 100:.2f}%"
                },
                "by_education": by_education,
                "overall": {
                    "education": "总体",
                    "type": "summary",
                    "satisfaction_ratio": overall_data["satisfaction_ratio"],
                    "total_count": overall_data["total_count"],
                    "satisfied_count": overall_data["satisfied_count"],
                    "percentage": f"{overall_data['satisfaction_ratio'] * 100:.2f}%",
                    "options_detail": overall_data["options_detail"]
                },
                "legacy_format": [{
                    "calcVals": [{
                        "val": legacy_results
                    }]
                }]
            }
        }

    except Exception as e:
        logger.error(f"计算指标 '各学历及总体，对母校的满意度' 时发生错误: {str(e)}", exc_info=True)
        return {
            "success": False,
            "message": f"数据获取失败: 各学历及总体，对母校的满意度",
            "code": 500,
            "error": str(e)
        }